package com.datamation.swadeshi.control.data;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.datamation.swadeshi.model.Debtor;
import com.datamation.swadeshi.model.Items;

public class DebtorDS {

	private SQLiteDatabase dB;
	private DatabaseHelper dbHelper;
	Context context;
	private String TAG="swadeshi";
	
	
	public DebtorDS (Context context){
		this.context = context;
		dbHelper = new DatabaseHelper(context);
	}
	
	public void open() throws SQLException {
		dB = dbHelper.getWritableDatabase();
	}
	
	@SuppressWarnings("static-access")
	public int createOrUpdateDebtor(ArrayList<Debtor>  debtors) {
		int serverdbID =0;		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
		
	try{
		
	for (Debtor debtor : debtors) {
					
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FDEBTOR 
				+ " WHERE " + dbHelper.FDEBTOR_CODE + " = '" +debtor.getFDEBTOR_CODE() + "'";		
		
		cursor = dB.rawQuery(selectQuery, null);
		
		ContentValues values = new ContentValues();
		
		values.put(dbHelper.FDEBTOR_CODE, debtor.getFDEBTOR_CODE());
		values.put(dbHelper.FDEBTOR_NAME, debtor.getFDEBTOR_NAME());
		values.put(dbHelper.FDEBTOR_ADD1, debtor.getFDEBTOR_ADD1());
		values.put(dbHelper.FDEBTOR_ADD2, debtor.getFDEBTOR_ADD2());
		values.put(dbHelper.FDEBTOR_ADD3, debtor.getFDEBTOR_ADD3());
		values.put(dbHelper.FDEBTOR_TELE, debtor.getFDEBTOR_TELE());
		values.put(dbHelper.FDEBTOR_MOB, debtor.getFDEBTOR_MOB());
		values.put(dbHelper.FDEBTOR_EMAIL, debtor.getFDEBTOR_EMAIL());
		values.put(dbHelper.FDEBTOR_CREATEDATE, debtor.getFDEBTOR_CREATEDATE());
		values.put(dbHelper.FDEBTOR_REM_DIS, debtor.getFDEBTOR_REM_DIS());
		values.put(dbHelper.FDEBTOR_TOWN_CODE, debtor.getFDEBTOR_TOWN_CODE());
		values.put(dbHelper.FDEBTOR_AREA_CODE, debtor.getFDEBTOR_AREA_CODE());
		values.put(dbHelper.FDEBTOR_DEB_CAT_CODE, debtor.getFDEBTOR_DEB_CAT_CODE());
		values.put(dbHelper.FDEBTOR_DBGR_CODE, debtor.getFDEBTOR_DBGR_CODE());
		values.put(dbHelper.FDEBTOR_DEB_CLS_CODE, debtor.getFDEBTOR_DEB_CLS_CODE());
		values.put(dbHelper.FDEBTOR_STATUS, debtor.getFDEBTOR_STATUS());
		values.put(dbHelper.FDEBTOR_LYLTY, debtor.getFDEBTOR_LYLTY());
		values.put(dbHelper.FDEBTOR_DEAL_CODE, debtor.getFDEBTOR_DEAL_CODE());
		values.put(dbHelper.FDEBTOR_ADD_USER, debtor.getFDEBTOR_ADD_USER());
		values.put(dbHelper.FDEBTOR_ADD_DATE_DEB, debtor.getFDEBTOR_ADD_DATE_DEB());
		values.put(dbHelper.FDEBTOR_ADD_MACH, debtor.getFDEBTOR_ADD_MACH());
		values.put(dbHelper.FDEBTOR_RECORD_ID, debtor.getFDEBTOR_RECORD_ID());
		values.put(dbHelper.FDEBTOR_TIME_STAMP, debtor.getFDEBTOR_TIME_STAMP());
		values.put(dbHelper.FDEBTOR_CRD_PERIOD, debtor.getFDEBTOR_CRD_PERIOD());
		values.put(dbHelper.FDEBTOR_CHK_CRD_PRD, debtor.getFDEBTOR_CHK_CRD_PRD());
		values.put(dbHelper.FDEBTOR_CRD_LIMIT, debtor.getFDEBTOR_CRD_LIMIT());
		values.put(dbHelper.FDEBTOR_CHK_CRD_LIMIT, debtor.getFDEBTOR_CHK_CRD_LIMIT());
		values.put(dbHelper.FDEBTOR_REP_CODE, debtor.getFDEBTOR_REP_CODE());
		values.put(dbHelper.FDEBTOR_RANK_CODE, debtor.getFDEBTOR_RANK_CODE());
		values.put(dbHelper.FDEBTOR_TRAN_DATE, debtor.getFDEBTOR_TRAN_DATE());
		values.put(dbHelper.FDEBTOR_TRAN_BATCH, debtor.getFDEBTOR_TRAN_BATCH());
		values.put(dbHelper.FDEBTOR_SUMMARY, debtor.getFDEBTOR_SUMMARY());
		
		values.put(dbHelper.FDEBTOR_OUT_DIS, debtor.getFDEBTOR_OUT_DIS());
		
		int count = cursor.getCount();
		Log.i(TAG , "Cursor ServerDatabase count:" + count);
		
			if(count>0)
			{
				serverdbID = dB.update(dbHelper.TABLE_FDEBTOR, values,dbHelper.FDEBTOR_CODE + " =?",	
						new String[] { String.valueOf(debtor.getFDEBTOR_CODE())});
			}else
			{
				serverdbID = (int) dB.insert(dbHelper.TABLE_FDEBTOR, null, values);
			}
		}
		}finally {  
			if (cursor !=null) {
				cursor.close();
			}
			dB.close();
		}
		return serverdbID;
				
	}
	
	public int deleteAll(){
		
		int count =0;
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		Cursor cursor = null;
		try{
			
			cursor = dB.rawQuery("SELECT * FROM " + dbHelper.TABLE_FDEBTOR, null);
			count =cursor.getCount();
			if(count>0){
				int success = dB.delete(dbHelper.TABLE_FDEBTOR, null, null);
				Log.v("Success", success+"");
			}
		}catch (Exception e){

			Log.v(TAG+" Exception", e.toString());
			
		}finally{  
			if (cursor !=null) {
				cursor.close();
			}
			dB.close();
		}
		
		return count;
		
	}

	public ArrayList<Debtor> getAllCustomers() {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Debtor> list = new ArrayList<Debtor>();
		
		String selectQuery = "select * from "+dbHelper.TABLE_FDEBTOR;
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			Debtor aDebtor=new Debtor();
		
			aDebtor.setFDEBTOR_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ID)));
			aDebtor.setFDEBTOR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CODE)));
			aDebtor.setFDEBTOR_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME)));
			aDebtor.setFDEBTOR_ADD1(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD1)));
			aDebtor.setFDEBTOR_ADD2(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD2)));
			aDebtor.setFDEBTOR_ADD3(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD3)));
			aDebtor.setFDEBTOR_TELE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TELE)));
			aDebtor.setFDEBTOR_MOB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_MOB)));
			aDebtor.setFDEBTOR_EMAIL(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_EMAIL)));
			aDebtor.setFDEBTOR_CREATEDATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CREATEDATE)));
			aDebtor.setFDEBTOR_REM_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REM_DIS)));
			aDebtor.setFDEBTOR_TOWN_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TOWN_CODE)));
			aDebtor.setFDEBTOR_AREA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_AREA_CODE)));
			aDebtor.setFDEBTOR_DEB_CAT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CAT_CODE)));
			aDebtor.setFDEBTOR_DBGR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DBGR_CODE)));
			aDebtor.setFDEBTOR_DEB_CLS_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CLS_CODE)));
			aDebtor.setFDEBTOR_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_STATUS)));
			aDebtor.setFDEBTOR_LYLTY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_LYLTY)));
			aDebtor.setFDEBTOR_DEAL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEAL_CODE)));
			aDebtor.setFDEBTOR_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_USER)));
			aDebtor.setFDEBTOR_ADD_DATE_DEB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_DATE_DEB)));
			aDebtor.setFDEBTOR_ADD_MACH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_MACH)));
			aDebtor.setFDEBTOR_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RECORD_ID)));
			aDebtor.setFDEBTOR_TIME_STAMP(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TIME_STAMP)));
			aDebtor.setFDEBTOR_CRD_PERIOD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_PERIOD)));
			aDebtor.setFDEBTOR_CHK_CRD_PRD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_PRD)));
			aDebtor.setFDEBTOR_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_LIMIT)));
			aDebtor.setFDEBTOR_CHK_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_LIMIT)));
			aDebtor.setFDEBTOR_REP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REP_CODE)));
			aDebtor.setFDEBTOR_RANK_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RANK_CODE)));
			aDebtor.setFDEBTOR_TRAN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_DATE)));
			aDebtor.setFDEBTOR_TRAN_BATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_BATCH)));
			aDebtor.setFDEBTOR_SUMMARY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_SUMMARY)));
			aDebtor.setFDEBTOR_OUT_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_OUT_DIS)));
							
			list.add(aDebtor);
			
		}
		
		return list;
	}

	public ArrayList<Debtor> getCustomerByCodeAndName(String newText) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Debtor> list = new ArrayList<Debtor>();
		
		String selectQuery = "select * from "+dbHelper.TABLE_FDEBTOR+
				" where "+dbHelper.FDEBTOR_CODE+" || "+dbHelper.FDEBTOR_NAME+" like '%"+newText+"%'";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			Debtor aDebtor=new Debtor();
		
			aDebtor.setFDEBTOR_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ID)));
			aDebtor.setFDEBTOR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CODE)));
			aDebtor.setFDEBTOR_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME)));
			aDebtor.setFDEBTOR_ADD1(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD1)));
			aDebtor.setFDEBTOR_ADD2(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD2)));
			aDebtor.setFDEBTOR_ADD3(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD3)));
			aDebtor.setFDEBTOR_TELE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TELE)));
			aDebtor.setFDEBTOR_MOB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_MOB)));
			aDebtor.setFDEBTOR_EMAIL(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_EMAIL)));
			aDebtor.setFDEBTOR_CREATEDATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CREATEDATE)));
			aDebtor.setFDEBTOR_REM_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REM_DIS)));
			aDebtor.setFDEBTOR_TOWN_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TOWN_CODE)));
			aDebtor.setFDEBTOR_AREA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_AREA_CODE)));
			aDebtor.setFDEBTOR_DEB_CAT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CAT_CODE)));
			aDebtor.setFDEBTOR_DBGR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DBGR_CODE)));
			aDebtor.setFDEBTOR_DEB_CLS_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CLS_CODE)));
			aDebtor.setFDEBTOR_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_STATUS)));
			aDebtor.setFDEBTOR_LYLTY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_LYLTY)));
			aDebtor.setFDEBTOR_DEAL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEAL_CODE)));
			aDebtor.setFDEBTOR_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_USER)));
			aDebtor.setFDEBTOR_ADD_DATE_DEB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_DATE_DEB)));
			aDebtor.setFDEBTOR_ADD_MACH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_MACH)));
			aDebtor.setFDEBTOR_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RECORD_ID)));
			aDebtor.setFDEBTOR_TIME_STAMP(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TIME_STAMP)));
			aDebtor.setFDEBTOR_CRD_PERIOD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_PERIOD)));
			aDebtor.setFDEBTOR_CHK_CRD_PRD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_PRD)));
			aDebtor.setFDEBTOR_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_LIMIT)));
			aDebtor.setFDEBTOR_CHK_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_LIMIT)));
			aDebtor.setFDEBTOR_REP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REP_CODE)));
			aDebtor.setFDEBTOR_RANK_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RANK_CODE)));
			aDebtor.setFDEBTOR_TRAN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_DATE)));
			aDebtor.setFDEBTOR_TRAN_BATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_BATCH)));
			aDebtor.setFDEBTOR_SUMMARY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_SUMMARY)));
			aDebtor.setFDEBTOR_OUT_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_OUT_DIS)));
							
			list.add(aDebtor);
			
		}
		
		return list;
	}
	
	
	public ArrayList<Debtor> getRouteCustomers(String RouteCode) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Debtor> list = new ArrayList<Debtor>();
		
		String selectQuery = "SELECT * FROM "+dbHelper.TABLE_FROUTEDET +" RD, "+dbHelper.TABLE_FDEBTOR+" D WHERE RD."+dbHelper.FROUTEDET_DEB_CODE+"=D."+dbHelper.FDEBTOR_CODE +" AND RD."+dbHelper.FROUTEDET_ROUTE_CODE+ "='"+RouteCode+"'";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		while(cursor.moveToNext()){
			
			Debtor aDebtor=new Debtor();
		
			aDebtor.setFDEBTOR_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ID)));
			aDebtor.setFDEBTOR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CODE)));
			aDebtor.setFDEBTOR_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME)));
			aDebtor.setFDEBTOR_ADD1(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD1)));
			aDebtor.setFDEBTOR_ADD2(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD2)));
			aDebtor.setFDEBTOR_ADD3(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD3)));
			aDebtor.setFDEBTOR_TELE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TELE)));
			aDebtor.setFDEBTOR_MOB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_MOB)));
			aDebtor.setFDEBTOR_EMAIL(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_EMAIL)));
			aDebtor.setFDEBTOR_CREATEDATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CREATEDATE)));
			aDebtor.setFDEBTOR_REM_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REM_DIS)));
			aDebtor.setFDEBTOR_TOWN_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TOWN_CODE)));
			aDebtor.setFDEBTOR_AREA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_AREA_CODE)));
			aDebtor.setFDEBTOR_DEB_CAT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CAT_CODE)));
			aDebtor.setFDEBTOR_DBGR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DBGR_CODE)));
			aDebtor.setFDEBTOR_DEB_CLS_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CLS_CODE)));
			aDebtor.setFDEBTOR_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_STATUS)));
			aDebtor.setFDEBTOR_LYLTY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_LYLTY)));
			aDebtor.setFDEBTOR_DEAL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEAL_CODE)));
			aDebtor.setFDEBTOR_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_USER)));
			aDebtor.setFDEBTOR_ADD_DATE_DEB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_DATE_DEB)));
			aDebtor.setFDEBTOR_ADD_MACH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_MACH)));
			aDebtor.setFDEBTOR_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RECORD_ID)));
			aDebtor.setFDEBTOR_TIME_STAMP(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TIME_STAMP)));
			aDebtor.setFDEBTOR_CRD_PERIOD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_PERIOD)));
			aDebtor.setFDEBTOR_CHK_CRD_PRD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_PRD)));
			aDebtor.setFDEBTOR_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_LIMIT)));
			aDebtor.setFDEBTOR_CHK_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_LIMIT)));
			aDebtor.setFDEBTOR_REP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REP_CODE)));
			aDebtor.setFDEBTOR_RANK_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RANK_CODE)));
			aDebtor.setFDEBTOR_TRAN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_DATE)));
			aDebtor.setFDEBTOR_TRAN_BATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_BATCH)));
			aDebtor.setFDEBTOR_SUMMARY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_SUMMARY)));
			aDebtor.setFDEBTOR_OUT_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_OUT_DIS)));
							
			list.add(aDebtor);
			
		}
		
		return list;
	}

	
	public ArrayList<Debtor> getRouteCustomersByCodeAndName(String RouteCode,String newText) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Debtor> list = new ArrayList<Debtor>();
		
		String selectQuery = "SELECT * FROM "+dbHelper.TABLE_FROUTEDET +" RD, "
		+dbHelper.TABLE_FDEBTOR+" D WHERE RD."+dbHelper.FROUTEDET_DEB_CODE+"=D."+dbHelper.FDEBTOR_CODE 
		+" AND RD."+dbHelper.FROUTEDET_ROUTE_CODE+ "='"+RouteCode+"' AND D."+dbHelper.FDEBTOR_CODE+" || D."+dbHelper.FDEBTOR_NAME+" like '%"+newText+"%'";;
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){
			
			Debtor aDebtor=new Debtor();
		
			aDebtor.setFDEBTOR_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ID)));
			aDebtor.setFDEBTOR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CODE)));
			aDebtor.setFDEBTOR_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME)));
			aDebtor.setFDEBTOR_ADD1(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD1)));
			aDebtor.setFDEBTOR_ADD2(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD2)));
			aDebtor.setFDEBTOR_ADD3(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD3)));
			aDebtor.setFDEBTOR_TELE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TELE)));
			aDebtor.setFDEBTOR_MOB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_MOB)));
			aDebtor.setFDEBTOR_EMAIL(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_EMAIL)));
			aDebtor.setFDEBTOR_CREATEDATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CREATEDATE)));
			aDebtor.setFDEBTOR_REM_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REM_DIS)));
			aDebtor.setFDEBTOR_TOWN_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TOWN_CODE)));
			aDebtor.setFDEBTOR_AREA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_AREA_CODE)));
			aDebtor.setFDEBTOR_DEB_CAT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CAT_CODE)));
			aDebtor.setFDEBTOR_DBGR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DBGR_CODE)));
			aDebtor.setFDEBTOR_DEB_CLS_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CLS_CODE)));
			aDebtor.setFDEBTOR_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_STATUS)));
			aDebtor.setFDEBTOR_LYLTY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_LYLTY)));
			aDebtor.setFDEBTOR_DEAL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEAL_CODE)));
			aDebtor.setFDEBTOR_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_USER)));
			aDebtor.setFDEBTOR_ADD_DATE_DEB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_DATE_DEB)));
			aDebtor.setFDEBTOR_ADD_MACH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_MACH)));
			aDebtor.setFDEBTOR_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RECORD_ID)));
			aDebtor.setFDEBTOR_TIME_STAMP(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TIME_STAMP)));
			aDebtor.setFDEBTOR_CRD_PERIOD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_PERIOD)));
			aDebtor.setFDEBTOR_CHK_CRD_PRD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_PRD)));
			aDebtor.setFDEBTOR_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_LIMIT)));
			aDebtor.setFDEBTOR_CHK_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_LIMIT)));
			aDebtor.setFDEBTOR_REP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REP_CODE)));
			aDebtor.setFDEBTOR_RANK_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RANK_CODE)));
			aDebtor.setFDEBTOR_TRAN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_DATE)));
			aDebtor.setFDEBTOR_TRAN_BATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_BATCH)));
			aDebtor.setFDEBTOR_SUMMARY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_SUMMARY)));
			aDebtor.setFDEBTOR_OUT_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_OUT_DIS)));
							
			list.add(aDebtor);
			
		}
		
		return list;
	}
	
	
	public Debtor getSelectedCustomerByCode(String code) {
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		//ArrayList<Debtor> list = new ArrayList<Debtor>();
		
		String selectQuery = "select * from "+dbHelper.TABLE_FDEBTOR +" Where "+dbHelper.FDEBTOR_CODE+"='"+code+"'";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){
			
			Debtor aDebtor=new Debtor();
		
			aDebtor.setFDEBTOR_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ID)));
			aDebtor.setFDEBTOR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CODE)));
			aDebtor.setFDEBTOR_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME)));
			aDebtor.setFDEBTOR_ADD1(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD1)));
			aDebtor.setFDEBTOR_ADD2(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD2)));
			aDebtor.setFDEBTOR_ADD3(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD3)));
			aDebtor.setFDEBTOR_TELE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TELE)));
			aDebtor.setFDEBTOR_MOB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_MOB)));
			aDebtor.setFDEBTOR_EMAIL(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_EMAIL)));
			aDebtor.setFDEBTOR_CREATEDATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CREATEDATE)));
			aDebtor.setFDEBTOR_REM_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REM_DIS)));
			aDebtor.setFDEBTOR_TOWN_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TOWN_CODE)));
			aDebtor.setFDEBTOR_AREA_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_AREA_CODE)));
			aDebtor.setFDEBTOR_DEB_CAT_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CAT_CODE)));
			aDebtor.setFDEBTOR_DBGR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DBGR_CODE)));
			aDebtor.setFDEBTOR_DEB_CLS_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEB_CLS_CODE)));
			aDebtor.setFDEBTOR_STATUS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_STATUS)));
			aDebtor.setFDEBTOR_LYLTY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_LYLTY)));
			aDebtor.setFDEBTOR_DEAL_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_DEAL_CODE)));
			aDebtor.setFDEBTOR_ADD_USER(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_USER)));
			aDebtor.setFDEBTOR_ADD_DATE_DEB(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_DATE_DEB)));
			aDebtor.setFDEBTOR_ADD_MACH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_ADD_MACH)));
			aDebtor.setFDEBTOR_RECORD_ID(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RECORD_ID)));
			aDebtor.setFDEBTOR_TIME_STAMP(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TIME_STAMP)));
			aDebtor.setFDEBTOR_CRD_PERIOD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_PERIOD)));
			aDebtor.setFDEBTOR_CHK_CRD_PRD(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_PRD)));
			aDebtor.setFDEBTOR_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CRD_LIMIT)));
			aDebtor.setFDEBTOR_CHK_CRD_LIMIT(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CHK_CRD_LIMIT)));
			aDebtor.setFDEBTOR_REP_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_REP_CODE)));
			aDebtor.setFDEBTOR_RANK_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_RANK_CODE)));
			aDebtor.setFDEBTOR_TRAN_DATE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_DATE)));
			aDebtor.setFDEBTOR_TRAN_BATCH(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_TRAN_BATCH)));
			aDebtor.setFDEBTOR_SUMMARY(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_SUMMARY)));
			aDebtor.setFDEBTOR_OUT_DIS(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_OUT_DIS)));
							
			return aDebtor;
			
		}
		return null;
		
		
	}
	
	
	@SuppressWarnings("static-access")
	public String getCustNameByCode(String code){
		
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FDEBTOR +" WHERE "+dbHelper.FDEBTOR_CODE +"='"+code+"'";
		
		Cursor cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){

			return cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME));
			
		}
			
		return "";
	}
	
	public ArrayList<Debtor> getDebDetails(String searchword) 
	{
		if (dB == null) {
			open();
		} else if (!dB.isOpen()) {
			open();
		}
		
		ArrayList<Debtor> Itemname = new ArrayList<Debtor>();

		String selectQuery = "select DebName,DebCode from fDebtor where DebCode LIKE '%"+searchword+"%' OR DebName LIKE '%"+searchword+"%'";
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext())
		{
			Debtor items=new Debtor();
			
			items.setFDEBTOR_NAME(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME)));
			items.setFDEBTOR_CODE(cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_CODE)));
			Itemname.add(items);
		}
		
		return Itemname;
	}
	public String getDebNameByCode(String code){
		
		if(dB == null){
			open();
		}else if(!dB.isOpen()){
			open();
		}
		
		String selectQuery = "SELECT * FROM " + dbHelper.TABLE_FDEBTOR +" WHERE "+dbHelper.FDEBTOR_CODE+"='"+code+"'" ;
		
		Cursor cursor = null;
		cursor = dB.rawQuery(selectQuery, null);
		
		while(cursor.moveToNext()){

			return cursor.getString(cursor.getColumnIndex(dbHelper.FDEBTOR_NAME));
			
		}
		return "";
		
	}
	
}
